/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package Data;

import java.io.InputStream;

import java.io.File;  
  
import java.io.FileInputStream;  
  
import java.io.FileNotFoundException;  
  
import java.io.IOException;  
  
import java.text.DecimalFormat;  
  
import java.text.SimpleDateFormat;  
  
import java.util.ArrayList;  
  
import java.util.Arrays;  
  
import java.util.Date;  
  
import java.util.List;  
  
  
import org.apache.poi.hssf.usermodel.HSSFCell;
  
import org.apache.poi.hssf.usermodel.HSSFDateUtil;  
  
import org.apache.poi.hssf.usermodel.HSSFRow;  
  
import org.apache.poi.hssf.usermodel.HSSFSheet;  
  
import org.apache.poi.hssf.usermodel.HSSFWorkbook; 

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.poifs.filesystem.POIFSFileSystem; 

/**
 *
 * @author Administrator
 */
public class ExcelOperator {
    /** 
	 
     *读取Excel的内容，第一维数组存储的是一行中格列的值，二维数组存储的是多少个行
     * @param file 读取数据的源Excel 
     * @return 读出的Excel中数据的内容 
     * @throws java.io.FileNotFoundException 
	 
     *@paramignoreRows读取数据忽略的行数，比喻行头不需要读入忽略的行数为1 
     * @throws InvalidFormatException 
	 
*@throwsFileNotFoundException 
	 
*@throwsIOException 
	 
	*/  
	  
	public static String[][] getData(File file)  
	  
	throws FileNotFoundException, IOException, InvalidFormatException {  
	  
		List<String[]> result = new ArrayList<>();  
		
		String[][] returnArray=null;
		
		int rowSize = 0;  
	    
		String filename=file.getName();
		
		String fileEx=filename.substring(filename.lastIndexOf('.')+1);
		
		InputStream in;
                in = new FileInputStream(file);
		
		// 打开HSSFWorkbook 
		
		if(fileEx.equalsIgnoreCase("xls"))
		{
			POIFSFileSystem fs = new POIFSFileSystem(in);
	  
			HSSFWorkbook wb = new HSSFWorkbook(fs);
	  
			HSSFCell cell = null;
	  
			for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {  
	  
				HSSFSheet st = wb.getSheetAt(sheetIndex);  
	  
				for (int rowIndex = 0; rowIndex <= st.getLastRowNum(); rowIndex++) {  
	  
					HSSFRow row = st.getRow(rowIndex);  
	  
					if (row ==null) {  
	  
						continue;  
	  
					}  
	  
					int tempRowSize = row.getLastCellNum() + 1;  
	  
					if (tempRowSize > rowSize) {  
	  
						rowSize = tempRowSize;  
	  
					}  
	  
					String[] values = new String[rowSize];  
	  
					Arrays.fill(values, "");  
	  
					boolean hasValue =false;  
	  
					for (short columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++) {  
	  
						String value = "";  
	  
						cell = row.getCell(columnIndex);  
	  
						if (cell !=null) {  
	  
							// 注意：一定要设成这个，否则可能会出现乱码  
	  
							//cell.setEncoding(HSSFCell.ENCODING_UTF_16);  
	  
							switch (cell.getCellType()) {  
	  
							case HSSFCell.CELL_TYPE_STRING:  
	  
								value = cell.getStringCellValue();  
	  
								break;  
	  
							case HSSFCell.CELL_TYPE_NUMERIC:  
	  
								if (HSSFDateUtil.isCellDateFormatted(cell)) {  
	  
									Date date = cell.getDateCellValue();  
	  
									if (date !=null) {  
	  
										value = new SimpleDateFormat("yyyy-MM-dd")  
	  
										.format(date);  
	  
									} else {  
	  
										value = "";  
	  
									}  
	  
								} else {  
	  
									value = new DecimalFormat("0").format(cell  
	  
											.getNumericCellValue());  
	  
								}  
	  
								break;  
	  
							case HSSFCell.CELL_TYPE_FORMULA:  
	  
								// 导入时如果为公式生成的数据则无值  
	  
								if (!cell.getStringCellValue().equals("")) {  
	  
									value = cell.getStringCellValue();  
	  
								} else {  
	  
									value = cell.getNumericCellValue() + "";  
	  
								}  
	  
								break;  
	  
							case HSSFCell.CELL_TYPE_BLANK:  
	  
								break;  
	  
							case HSSFCell.CELL_TYPE_ERROR:  
	  
								value = "";  
	  
								break;  
	  
							case HSSFCell.CELL_TYPE_BOOLEAN:  
	  
								value = (cell.getBooleanCellValue() == true ?"Y" : "N");  
	  
								break;  
	  
							default:  
	  
								value = "";  
	  
							}  
	  
						}  
	  
						if (columnIndex == 0 && value.trim().equals("")) {  
	  
							break;  
	  
						}  
	  
						values[columnIndex] = rightTrim(value);  
	  
						hasValue = true;  
	  
					}  
	  
	  
					if (hasValue) {  
	  
						result.add(values);  
	  
					}  
	  
				}  
	  
			}  
	  
			wb.close(); 
	  
			returnArray = new String[result.size()][rowSize];  
	  
			for (int i = 0; i < returnArray.length; i++) {  
	  
				returnArray[i] = (String[]) result.get(i);  
	  
			}  
			
		}
		else if(fileEx.equalsIgnoreCase("xlsx"))
		{
			XSSFWorkbook wb=null;
			
			if(in!=null){
				
				wb = new XSSFWorkbook(file);
				
			}
			  
			XSSFCell cell = null;
	  
			for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {  
	  
				XSSFSheet st = wb.getSheetAt(sheetIndex);  
	  
				for (int rowIndex = 0; rowIndex <= st.getLastRowNum(); rowIndex++) {  
	  
					XSSFRow row = st.getRow(rowIndex);  
	  
					if (row ==null) {  
	  
						continue;  
	  
					}  
	  
					int tempRowSize = row.getLastCellNum() + 1;  
	  
					if (tempRowSize > rowSize) {  
	  
						rowSize = tempRowSize;  
	  
					}  
	  
					String[] values = new String[rowSize];  
	  
					Arrays.fill(values, "");  
	  
					boolean hasValue =false;  
	  
					for (short columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++) {  
	  
						String value = "";  
	  
						cell = row.getCell(columnIndex);  
	  
						if (cell !=null) {  
	  
							// 注意：一定要设成这个，否则可能会出现乱码  
	  
							//cell.setEncoding(HSSFCell.ENCODING_UTF_16);  
	  
							switch (cell.getCellType()) {  
	  
							case XSSFCell.CELL_TYPE_STRING:  
	  
								value = cell.getStringCellValue();  
	  
								break;  
	  
							case XSSFCell.CELL_TYPE_NUMERIC:  
	  
								if (HSSFDateUtil.isCellDateFormatted(cell)) {  
	  
									Date date = cell.getDateCellValue();  
	  
									if (date !=null) {  
	  
										value = new SimpleDateFormat("yyyy-MM-dd")  
	  
										.format(date);  
	  
									} else {  
	  
										value = "";  
	  
									}  
	  
								} else {  
	  
									value = new DecimalFormat("0").format(cell  
	  
											.getNumericCellValue());  
	  
								}  
	  
								break;  
	  
							case XSSFCell.CELL_TYPE_FORMULA:  
	  
								// 导入时如果为公式生成的数据则无值  
	  
								if (!cell.getStringCellValue().equals("")) {  
	  
									value = cell.getStringCellValue();  
	  
								} else {  
	  
									value = cell.getNumericCellValue() + "";  
	  
								}  
	  
								break;  
	  
							case XSSFCell.CELL_TYPE_BLANK:  
	  
								break;  
	  
							case XSSFCell.CELL_TYPE_ERROR:  
	  
								value = "";  
	  
								break;  
	  
							case XSSFCell.CELL_TYPE_BOOLEAN:  
	  
								value = (cell.getBooleanCellValue() == true ?"Y" : "N");  
	  
								break;  
	  
							default:  
	  
								value = "";  
	  
							}  
	  
						}  
	  
						if (columnIndex == 0 && value.trim().equals("")) {  
	  
							break;  
	  
						}  
	  
						values[columnIndex] = rightTrim(value);  
	  
						hasValue = true;  
	  
					}  
	  
	  
					if (hasValue) {  
	  
						result.add(values);  
	  
					}  
	  
				}  
	  
			}  
	  
			wb.close();
	  
			returnArray = new String[result.size()][rowSize];  
	  
			for (int i = 0; i < returnArray.length; i++) {  
	  
				returnArray[i] = (String[]) result.get(i);  
	  
			}  

		}
		
		

		in.close();
		
		return returnArray;
		
	}  
	  
	  
	/** 
	 
	*去掉字符串右边的空格 
	 
	*@param str要处理的字符串 
	 
	*@return处理后的字符串 
	 
	*/  
	  
	public static String rightTrim(String str) {  
		if (str ==null) 
		{  
			return"";    
		}  	  
		int length = str.length();  
		for (int i = length - 1; i >= 0; i--)
		{  
			if (str.charAt(i) != 0x20) 
			{    
				break;  
			}  
			length--;  
		}   
		return str.substring(0, length);  
	}  
}
